from fastapi import APIRouter, Depends, HTTPException, status, Query, Path
from sqlalchemy import func, text
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql import select
from typing import List, Optional, Dict, Any
from datetime import datetime
import json

from app.models.db import User, get_async_session
from app.models.db import DataSource, DataSet, DataField, FieldChangeLog
from app.services.datasource_service import (
    test_datasource_connection, 
    get_database_objects,
    get_table_columns,
    get_datasource_sample,
    get_column_statistics
)
from app.models.user import current_active_user, fastapi_users

router = APIRouter(
    prefix="/datasources",
    tags=["数据源管理"]
)


# ==================== 数据源管理 ====================

@router.post("/", response_model=Dict[str, Any])
async def create_datasource(
    datasource_data: Dict[str, Any],
    session: AsyncSession = Depends(get_async_session)
):
    """创建数据源
    
    Args:
        datasource_data: 数据源数据
    """
    try:
        # 创建数据源对象
        datasource = DataSource(
            name=datasource_data["name"],
            description=datasource_data.get("description"),
            type=datasource_data["type"],
            host=datasource_data["host"],
            port=datasource_data["port"],
            database=datasource_data["database"],
            username=datasource_data["username"],
            use_ssl=datasource_data.get("use_ssl", False),
            ssl_ca=datasource_data.get("ssl_ca"),
            ssl_cert=datasource_data.get("ssl_cert"),
            ssl_key=datasource_data.get("ssl_key"),
            created_at=datetime.now(),
            connection_status="unknown"
        )
        
        # 加密密码
        datasource.set_password(datasource_data["password"])
        
        # 添加到数据库
        session.add(datasource)
        await session.commit()
        await session.refresh(datasource)
        
        return {
            "success": True,
            "message": "数据源创建成功",
            "data": datasource.to_dict()
        }
    
    except Exception as e:
        await session.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"创建数据源失败: {str(e)}"
        )


@router.get("/", response_model=Dict[str, Any])
async def get_datasources(
    skip: int = Query(0, ge=0),
    limit: int = Query(100, ge=1, le=1000),
    search: Optional[str] = None,
    session: AsyncSession = Depends(get_async_session)
):
    """获取数据源列表
    
    Args:
        skip: 跳过记录数
        limit: 返回记录数上限
        search: 搜索关键词
    """
    try:
        # 导入selectinload
        from sqlalchemy.orm import selectinload
        
        # 构建查询，预加载datasets关系
        query = select(DataSource).options(
            selectinload(DataSource.datasets)
        ).order_by(DataSource.created_at.desc())
        
        # 如果有搜索关键词，增加搜索条件
        if search:
            query = query.filter(
                DataSource.name.ilike(f"%{search}%") |
                DataSource.description.ilike(f"%{search}%") |
                DataSource.host.ilike(f"%{search}%") |
                DataSource.database.ilike(f"%{search}%")
            )
        
        # 计算总数 - 使用子查询方式
        total_query = select(func.count()).select_from(query.subquery())
        total_count = await session.scalar(total_query)
        
        # 执行查询
        result = await session.execute(query.offset(skip).limit(limit))
        datasources = result.scalars().all()
        
        # 构建返回结果 - 使用to_dict而不是dict
        return {
            "success": True,
            "items": [ds.to_dict(include_relationships=True) for ds in datasources],
            "total": total_count,
            "skip": skip,
            "limit": limit
        }
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取数据源列表失败: {str(e)}"
        )


@router.get("/{datasource_id}", response_model=Dict[str, Any])
async def get_datasource(
    datasource_id: str = Path(..., title="数据源ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取数据源详情
    
    Args:
        datasource_id: 数据源ID
    """
    try:
        # 导入需要的加载器
        from sqlalchemy.orm import selectinload, subqueryload
        
        # 查询数据源，预加载datasets关系及其相关联系
        result = await session.execute(
            select(DataSource).options(
                selectinload(DataSource.datasets).selectinload(DataSet.fields)
            ).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 构建完整的数据源信息字典（避免懒加载）
        datasource_dict = datasource.to_dict()
        
        # 手动构建datasets列表，避免懒加载
        datasets_list = []
        for ds in datasource.datasets:
            ds_dict = ds.to_dict()
            # 如果需要加载字段，手动添加预加载的字段信息
            fields_list = []
            for field in ds.fields:
                fields_list.append(field.to_dict())
            ds_dict["fields"] = fields_list
            datasets_list.append(ds_dict)
        
        datasource_dict["datasets"] = datasets_list
        
        return {
            "success": True,
            "item": datasource_dict
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取数据源详情失败: {str(e)}"
        )


@router.put("/{datasource_id}", response_model=Dict[str, Any])
async def update_datasource(
    datasource_data: Dict[str, Any],
    datasource_id: str = Path(..., title="数据源ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """更新数据源
    
    Args:
        datasource_id: 数据源ID
        datasource_data: 更新的数据源数据
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 更新基本信息
        if "name" in datasource_data:
            datasource.name = datasource_data["name"]
        
        if "description" in datasource_data:
            datasource.description = datasource_data["description"]
        
        if "type" in datasource_data:
            datasource.type = datasource_data["type"]
        
        if "host" in datasource_data:
            datasource.host = datasource_data["host"]
        
        if "port" in datasource_data:
            datasource.port = datasource_data["port"]
        
        if "database" in datasource_data:
            datasource.database = datasource_data["database"]
        
        if "username" in datasource_data:
            datasource.username = datasource_data["username"]
        
        # 更新密码（如果提供）
        if "password" in datasource_data and datasource_data["password"]:
            datasource.set_password(datasource_data["password"])
        
        # 更新SSL配置
        if "use_ssl" in datasource_data:
            datasource.use_ssl = datasource_data["use_ssl"]
        
        if "ssl_ca" in datasource_data:
            datasource.ssl_ca = datasource_data["ssl_ca"]
        
        if "ssl_cert" in datasource_data:
            datasource.ssl_cert = datasource_data["ssl_cert"]
        
        if "ssl_key" in datasource_data:
            datasource.ssl_key = datasource_data["ssl_key"]
        
        # 更新状态
        datasource.updated_at = datetime.now()
        datasource.connection_status = "unknown"  # 重置连接状态
        
        # 保存到数据库
        session.add(datasource)
        await session.commit()
        await session.refresh(datasource)
        
        return {
            "success": True,
            "message": "数据源更新成功",
            "data": datasource.to_dict()
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        await session.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"更新数据源失败: {str(e)}"
        )


@router.delete("/{datasource_id}", response_model=Dict[str, Any])
async def delete_datasource(
    datasource_id: str = Path(..., title="数据源ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """删除数据源
    
    Args:
        datasource_id: 数据源ID
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 1. 查找与此数据源关联的所有数据集ID
        dataset_result = await session.execute(
            select(DataSet.id).filter(DataSet.data_source_id == datasource_id)
        )
        dataset_ids = [str(row[0]) for row in dataset_result.all()]
        
        # 2. 删除所有与这些数据集相关的角色数据集访问权限记录
        if dataset_ids:
            # 为防止SQL注入，这里使用参数化查询而不是直接拼接字符串
            placeholders = ','.join([f"'{dataset_id}'" for dataset_id in dataset_ids])
            await session.execute(
                text(f"DELETE FROM dk_role_dataset_access WHERE dataset_id IN ({placeholders})")
            )
        
        # 3. 删除数据源（级联删除会自动删除关联的数据集和字段）
        await session.delete(datasource)
        await session.commit()
        
        return {
            "success": True,
            "message": "数据源删除成功"
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        await session.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"删除数据源失败: {str(e)}"
        )


@router.post("/{datasource_id}/test", response_model=Dict[str, Any])
async def test_connection(
    datasource_id: str = Path(..., title="数据源ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """测试数据源连接
    
    Args:
        datasource_id: 数据源ID
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 测试连接
        test_result = await test_datasource_connection(datasource)
        
        # 更新数据源状态
        session.add(datasource)
        await session.commit()
        
        return {
            "success": test_result["success"],
            "message": test_result["message"]
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        await session.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"测试数据源连接失败: {str(e)}"
        )


@router.get("/{datasource_id}/objects", response_model=Dict[str, Any])
async def get_objects(
    datasource_id: str = Path(..., title="数据源ID"),
    object_type: str = Query("table", description="对象类型：table或view"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取数据源对象列表（表或视图）
    
    Args:
        datasource_id: 数据源ID
        object_type: 对象类型，table或view
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 获取对象列表
        objects = await get_database_objects(datasource, object_type)
        
        return {
            "success": True,
            "data": objects
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取数据源对象列表失败: {str(e)}"
        )


@router.get("/{datasource_id}/tables/{table_name}/columns", response_model=Dict[str, Any])
async def get_columns(
    datasource_id: str = Path(..., title="数据源ID"),
    table_name: str = Path(..., title="表名"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取表或视图的列信息
    
    Args:
        datasource_id: 数据源ID
        table_name: 表名或视图名
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 获取列信息
        columns = await get_table_columns(datasource, table_name)
        
        return {
            "success": True,
            "data": columns
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取表列信息失败: {str(e)}"
        )


@router.get("/{datasource_id}/tables/{table_name}/sample", response_model=Dict[str, Any])
async def get_table_sample(
    datasource_id: str = Path(..., title="数据源ID"),
    table_name: str = Path(..., title="表名"),
    limit: int = Query(10, ge=1, le=100),
    session: AsyncSession = Depends(get_async_session)
):
    """获取表的样本数据
    
    Args:
        datasource_id: 数据源ID
        table_name: 表名
        limit: 返回的记录数上限
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 获取样本数据 - 使用新方法
        sample_data = await get_datasource_sample(datasource, table_name, limit)
        
        return {
            "success": True,
            "data": sample_data
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取表样本数据失败: {str(e)}"
        )


@router.get("/{datasource_id}/tables/{table_name}/columns/{column_name}/stats", response_model=Dict[str, Any])
async def get_column_stats(
    datasource_id: str = Path(..., title="数据源ID"),
    table_name: str = Path(..., title="表名"),
    column_name: str = Path(..., title="列名"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取表列的统计信息
    
    Args:
        datasource_id: 数据源ID
        table_name: 表名
        column_name: 列名
    """
    try:
        # 查询数据源
        result = await session.execute(
            select(DataSource).filter(DataSource.id == datasource_id)
        )
        datasource = result.scalar_one_or_none()
        
        if not datasource:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据源不存在: {datasource_id}"
            )
        
        # 获取列统计信息
        stats = await get_column_statistics(datasource, table_name, column_name)
        
        return {
            "success": True,
            "data": stats
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取列统计信息失败: {str(e)}"
        ) 